/*
* 数据库操作示例代码：
*    1、表操作
*         创建表格  删除表格  查询表格
*    2、数据操作
*            查询表格信息
*            操作数据
*/




#include <stdio.h>
#include <stdlib.h>
#include <string.h>

#include <mysql/mysql.h>

void mysql_menu();//数据库操作主菜单
int create_table(MYSQL *  conn,char *table_name);//创建数据库中的表格
int drop_table(MYSQL * conn,char *table_name);//删除数据库中的表格
int select_from_mysql(MYSQL * conn,char *table_name);//查询数据库中的表格
int select_from_table(MYSQL * conn,char *table_name,const char *temp);//查找数据库中指定表格的详细信息
int insert_table_data(MYSQL * conn,char *table_name);//给表格中插入数据信息
int select_from(MYSQL * conn,const char *name);//公共调用函数（查找表中格式信息）
int drop_table_data(MYSQL * conn,char *table_name);//删除数据库中的表格信息





int main(int argc, const char* argv[])
{
	char sql_name[20] = "";
	// 初始化连接
	MYSQL * conn;
	conn = mysql_init(NULL);
	if (conn == NULL) {
		printf("mysql init failed\n");
		return -1;
	}
	printf("请输入你要连接的数据库名称:\n");
	scanf("%s",sql_name);
	getchar();
	//printf("调试的SQL语句: %s\n",sql_name);
	
	// 与mysql建立连接
	conn = mysql_real_connect(conn,"localhost","root","1",sql_name,0,NULL,0);
	if (conn == NULL) {
		printf("mysql connect failed\n");
		return -1;
	}

	// 执行sql语句
	int ret;
	//char sql_buf[128] = "";
	
	mysql_query(conn,"SET NAMES utf8"); // 设置中文字符集
	char table_name[1024] = "";
	char temp[1024] = "";
	while(1){
		int sel = 0;
		mysql_menu();
		scanf("%d",&sel);
		getchar();
		switch(sel){
			case 1:ret = create_table(conn,table_name);
			       if(ret == 0)printf("创建表格成功\n");
			       break;
			case 2:ret = drop_table(conn,table_name);
			       if(ret == 0)printf("删除表格成功\n");
			       break;
			case 3:ret = select_from_mysql(conn,table_name);//查询数据库中的表格
			       break;
			case 4:printf("请按照输入查找数据库中的表格名称\n");//查找数据库中指定表格内信息
			       fgets(temp,sizeof(temp) - 1,stdin); 
			       ret = select_from_table(conn,table_name,temp);
			       //if(ret == 0)printf("查找到该表格\n");
			       break;
			case 5:ret = insert_table_data(conn,table_name);
			       if(ret == 0)printf("插入信息成功\n");
			       break;
			case 6:ret = drop_table_data(conn,table_name);//删除数据库中的表格信息
			       if(ret == 0)printf("删除此条信息成功\n");
			       break;
			case 0:mysql_close(conn);
			       return 0;
			default:
				   printf("输入有误，请输入正确选项\n");
		}
		memset(temp,0,sizeof(temp)); 
		memset(table_name,0,sizeof(table_name)); 
		
	}
	// 断开连接
	mysql_close(conn);
	
    return 0;
}
void mysql_menu()
{
		printf("***************数据库操作***************\n");
		printf("**********[1]创建数据库表格*************\n");
		printf("**********[2]删除数据库表格*************\n");
		printf("**********[3]查询数据库表格*************\n");
		printf("**********[4]查询数据库指定表格信息******\n");
		printf("**********[5]插入数据库表格内信息********\n");
		printf("**********[6]删除数据库表格内信息********\n");
		printf("**********[0]退出**********************\n");
		printf("请输入对数据库进行的操作:\n");
}
int drop_table(MYSQL * conn,char *table_name)//删除数据库中的表格
{
	int ret = 0;
	char temp[1024] = "";
	strcat(table_name,"drop table ");
	printf("请按照输入删除数据库表格名\n");
	fgets(temp,sizeof(temp) - 1,stdin); 
	strcat(table_name,temp);
	//printf("调试的SQL语句: %s\n",table_name);
	#if 1
	ret = mysql_query(conn,table_name); // 执行sql语句
	if (ret != 0) {
		printf("数据库执行失败\n");
	}
	
	#endif
	return ret;
}
int create_table(MYSQL * conn,char *table_name)//创建数据库中的表格
{
	int ret = 0;
	char temp[1024] = "";
	strcat(table_name,"create table ");
	printf("请按照以下格式输入创建数据库表格名等表格内列的信息:\n");
	printf("格式:表名(列名1 列类型(大小),列名2 列类型(大小),列名n 列类型(大小)...）\n");
	printf("例:data(ID char(18),name char(20),passwd char(10));\n");
	fgets(temp,sizeof(temp) - 1,stdin); 
	strcat(table_name,temp);
	//sprintf(table_name, "create table %s;",temp);
	//printf("调试的SQL语句: %s\n",table_name);
	#if 1
	ret = mysql_query(conn,table_name); // 执行sql语句
	if (ret != 0) {
		printf("数据库执行失败\n");
	}
	#endif
	return ret;
}
int select_from_mysql(MYSQL * conn,char *table_name)//查询数据库内所有的表格
{
	
	int ret;
	strcat(table_name,"show tables;");
	//printf("调试的SQL语句: %s\n",table_name);
	#if 1
	ret = mysql_query(conn,table_name); // 执行sql语句
	if (ret != 0) {
		printf("查询失败\n");
	}
	#endif
	MYSQL_RES *res;
	res = mysql_store_result(conn); // 存储查询结果
	if (res == NULL) {
		printf("存储结果失败\n");
	}
	
	unsigned int count;
	count = mysql_field_count(conn);
	//printf("count = %d\n", count);
	
	unsigned int fields;
	fields = mysql_num_fields(res);
	//printf("fields : %d\n", fields);
	
	MYSQL_ROW row; // typedef char[][] MYSQL_ROW;
	int i;
	while (NULL != (row = mysql_fetch_row(res))) { // 获取一行结果
		for (i = 0;i < count;++i) {
			printf("| %s |", row[i]);
		}
		printf("\n");
	}
	// 结果空间释放
	mysql_free_result(res);
	return ret;
}
int select_from_table(MYSQL * conn,char *table_name,const char *temp)//查找数据库中指定表格内信息
{
	
	int ret;
	strcat(table_name,"select * from ");	
	strcat(table_name,temp);
	//printf("调试的SQL语句: %s\n",table_name);
	#if 1
	ret = mysql_query(conn,table_name); // 执行sql语句
	if (ret != 0) {
		printf("查询失败\n");
	}
	#endif
	MYSQL_RES *res;
	res = mysql_store_result(conn); // 存储查询结果
	if (res == NULL) {
		printf("存储结果失败\n");
	}
	
	unsigned int count;
	count = mysql_field_count(conn);
	//printf("count = %d\n", count);
	
	unsigned int fields;
	fields = mysql_num_fields(res);
	//printf("fields : %d\n", fields);
	
	MYSQL_ROW row; // typedef char[][] MYSQL_ROW;
	int i;
	while (NULL != (row = mysql_fetch_row(res))) { // 获取一行结果
		for (i = 0;i < count;++i) {
			printf("| %s |", row[i]);
		}
		printf("\n");
	}
	// 结果空间释放
	mysql_free_result(res);
	return ret;
}
int insert_table_data(MYSQL * conn,char *table_name)//给表格中插入信息
{
	int ret = 0;
	char name[20] = "";
	char temp[1024] = "";
	strcat(table_name,"insert into ");
	printf("请输入你要插入信息的表格名:\n");
	scanf("%s",name);
	getchar();
	strcat(table_name,name);
	
	printf("信息格式:按照表中格式信息插入\n");
	
	ret = select_from(conn,name);//调用公共调用函数
	printf("例:'张三','007','123456',... \n");
	printf("请输入你要插入信息:\n");
	fgets(temp,sizeof(temp) - 1,stdin); 
	strcat(table_name," values(");
	strcat(table_name,temp);
	strcat(table_name,");");
	//sprintf(table_name, "create table %s;",temp);
	//printf("调试的SQL语句: %s\n",table_name);
	#if 1
	ret = mysql_query(conn,table_name); // 执行sql语句
	if (ret != 0) {
		printf("数据库执行失败\n");
	}
	#endif
	return ret;
}
int select_from(MYSQL * conn,const char *name)//公共调用函数
{
	char table_name[128] = "";
	int ret;
	strcat(table_name,"desc ");	
	strcat(table_name,name);
	//printf("调试的SQL语句: %s\n",table_name);
	#if 1
	ret = mysql_query(conn,table_name); // 执行sql语句
	if (ret != 0) {
		printf("查询失败\n");
	}
	#endif
	MYSQL_RES *res;
	res = mysql_store_result(conn); // 存储查询结果
	if (res == NULL) {
		printf("存储结果失败\n");
	}
	
	unsigned int count;
	count = mysql_field_count(conn);
	//printf("count = %d\n", count);
	
	unsigned int fields;
	fields = mysql_num_fields(res);
	//printf("fields : %d\n", fields);
	
	MYSQL_ROW row; // typedef char[][] MYSQL_ROW;
	int i;
	printf("表格中的信息有\n");
	while (NULL != (row = mysql_fetch_row(res))) { // 获取一行结果
		for (i = 0;i < count;++i) {
			printf("| %s |", row[i]);
		}
		printf("\n");
	}
	// 结果空间释放
	mysql_free_result(res);
	
	return ret;
}
int drop_table_data(MYSQL * conn,char *table_name)//删除数据库中的表格信息
{
	int ret = 0;
	char temp[1024] = "";
	printf("请输入你要删除表中信息的表名\n");
	char ID[20] = "";
	char list[30] = ""; 
	fgets(temp,sizeof(temp),stdin); 
	temp[strlen(temp) - 1] = '\0';
	
	printf("请输入你要删除表中信息的列名\n");
	fgets(list,sizeof(list),stdin); 
	list[strlen(list) - 1] = '\0';
	printf("请输入你要删除表中信息列信息\n");
	fgets(ID,sizeof(ID),stdin); 
	ID[strlen(ID) - 1] = '\0';
	
	/*strcat(table_name,"delete from ");
	strcat(table_name,temp);
	strcat(table_name," where ");
	strcat(table_name,list);
	strcat(table_name," = '");
	strcat(table_name,ID);
	strcat(table_name,"'");*/
	sprintf(table_name,"delete from %s where %s = '%s'",temp,list,ID);
	
	//printf("调试的SQL语句: %s\n",table_name);
	#if 1
	ret = mysql_query(conn,table_name); // 执行sql语句
	if (ret != 0) {
		printf("数据库执行失败\n");
	}
	
	#endif
	return ret;
} 